package org.groupg.poi.excel.calc;

import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.lang.Console;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;

public class EvaluatorFormula {
    static final String excel01 = "01.xlsx";
    static final String excel02 = "02.xlsx";

    static final File xlsx01 = new File(ResourceUtil.getResource(excel01).getFile());
    static final File xlsx02 = new File(ResourceUtil.getResource(excel02).getFile());

    public static void main(String[] args) throws IOException, InvalidFormatException {

        XSSFWorkbook workbook01 = XSSFWorkbookFactory.createWorkbook(OPCPackage.open(xlsx01));
        XSSFWorkbook workbook02 = XSSFWorkbookFactory.createWorkbook(OPCPackage.open(xlsx02));


        Sheet sheet = workbook01.getSheetAt(1);
        sheet.createRow(0).createCell(0).setCellFormula("G4A!K21");
        XSSFCreationHelper helper01 = workbook01.getCreationHelper();
        XSSFCreationHelper helper02 = workbook02.getCreationHelper();

        XSSFFormulaEvaluator evaluator01 = helper01.createFormulaEvaluator();
        XSSFFormulaEvaluator evaluator02 = helper02.createFormulaEvaluator();
        evaluator01.evaluateAll();
        Console.log("计算后的单元格值为:{}", workbook01.getSheetAt(1).getRow(0).getCell(0).getRawValue());

//        workbook01.write(Files.newOutputStream(xlsx01.toPath())); // 不能将读取的Excel再写进去，文件被占用

//        Map<String, FormulaEvaluator> formulas = new HashMap<>();
//        formulas.put(excel01, evaluator01);
//        formulas.put(excel02, evaluator02);
//
//        evaluator01.setupReferencedWorkbooks(formulas);
//        evaluator02.setupReferencedWorkbooks(formulas);
//        evaluator02.evaluateAll();


    }
}

